/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author SONY
 */
public class SaleOrderDetailDAO {
    
    DataSource ds;
    
    public SaleOrderDetailDAO() {
        try {
            Context context = new InitialContext();
            ds = (DataSource) context.lookup("java:comp/env/jdbc/FashionShopDatasource");
        } catch (Exception e) {
            System.err.println(e);
        }
    }
    
    public int insert (int SaleOrderID, String ProductID, int OrderQty, double LineTotal) {
        try {
            Connection conn = ds.getConnection();
            String sql = "INSERT INTO SaleOrderDetail ([SaleOrderID],[ProductID],[OrderQty],[LineTotal]) VALUES(?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, SaleOrderID);
            pstmt.setNString(2, ProductID);
            pstmt.setInt(3, OrderQty);
            pstmt.setDouble(4, LineTotal);
            if (pstmt.executeUpdate() > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                rs.next();
                return rs.getInt(1); //get SaleOrderDetailID just inserted
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return -1;
    }
    
    public ArrayList<SaleOrderDetail> search_bySaleOrderID(int SaleOrderID) {
        ArrayList<SaleOrderDetail> saleorderdetails = new ArrayList<SaleOrderDetail>();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM SaleOrderDetail WHERE SaleOrderID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, SaleOrderID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int SaleOrderDetailID = rs.getInt("SaleOrderDetailID");
                String ProductID = rs.getString("ProductID");
                int OrderQty = rs.getInt("OrderQty");
                double LineTotal = rs.getDouble("LineTotal");             
                SaleOrderDetail saleorderdetail = new SaleOrderDetail(SaleOrderDetailID, SaleOrderID, ProductID, OrderQty, LineTotal);
                saleorderdetails.add(saleorderdetail);              
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return saleorderdetails;
    }
    
}
